package com.advm.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.advm.bean.ADInfo;

public class VideoDAO {
	private String TABLE_NAME = "ADInfo";
	private Connection conn;
	private Statement st;
	ADInfo ad = new ADInfo();

	public VideoDAO() throws SQLException {
		this.conn = MySQLConn.getInstance().getConnection();
	}

	public void add(ADInfo ad) throws SQLException {
		st = conn.createStatement();
		String sql = "INSERT INTO " + TABLE_NAME + " VALUES(" + null + ",'" + ad.getAdName() + "','" + ad.getLable()
				+ "','" + ad.getLastTime() + "','" + ad.getSavePath() + "');";
		System.out.println(sql);
		st.executeUpdate(sql);
	}

	public String querry(String fileName) throws SQLException {
		st = conn.createStatement();
		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE adName ='" + fileName + "';";
		System.out.println("sql:" + sql);
		ResultSet set = st.executeQuery(sql);
		if (set.next()) {
			// String name = set.getString("adName");
			// String lable = set.getString("lable");
			// String lastTime = set.getString("lastTime");
			// String savePath = set.getString("savePath");
			// return new ADInfo(name,savePath,lable,lastTime);
			System.out.println(set.getString("savePath"));
			return set.getString("savePath");
		}

		return null;
	}

	public List<ADInfo> querryAll() throws SQLException {
		String sql = "SELECT * FROM " + TABLE_NAME + ";";
		st = conn.createStatement();
		ResultSet set = st.executeQuery(sql);
		List<ADInfo> list = new ArrayList<ADInfo>();
		while (set.next()) {
			ADInfo info = new ADInfo();
			info.setId(set.getInt("id"));
			info.setAd_name(set.getString("adName"));
			info.setLable(set.getString("lable"));
			info.setLastTime(set.getString("lastTime"));
			info.setSavePath(set.getString("savePath"));
			list.add(info);
		}
		return list;
	}

	public int delete(int id) throws SQLException {
		
		String sql = "DELETE FROM "+TABLE_NAME +" WHERE id = "+id+";";
		System.out.println(sql);
		st = conn.createStatement();
		int msg = st.executeUpdate(sql);
		return msg;
	}

	public int getCountADItems() throws SQLException {
		int sumCount = 0;
		String sql = "select count(*) as sum from " + TABLE_NAME + ";";
		st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		while(rs.next())
		{
			sumCount = rs.getInt("sum");
		}
		return sumCount;
	}

	public List<ADInfo> getPageADItems(int pageNum, int rowsPerPage) throws SQLException {
		
		String sql = "select * from " + TABLE_NAME + " limit " + (pageNum - 1)* rowsPerPage + "," + rowsPerPage + ";";
		st = conn.createStatement();
		ResultSet set = st.executeQuery(sql);
		List<ADInfo> list = new ArrayList<ADInfo>();
		while(set.next())
		{
			ADInfo info = new ADInfo();
			info.setId(set.getInt("id"));
			info.setAd_name(set.getString("adName"));
			info.setLable(set.getString("lable"));
			info.setLastTime(set.getString("lastTime"));
			info.setSavePath(set.getString("savePath"));
			list.add(info);
		}
		return list;
	}

}
